/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.querie.collab;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

import com.querie.db.QueryFragmentFactory;
import com.querie.db.DBMSConnection;
import com.querie.parser.Query;
import com.querie.beans.DatabaseAccess;
import com.querie.pojo.QueryItemResult;
import com.querie.parser.exception.*;
import com.querie.pojo.RecommendedQuery;
import com.querie.util.QryParameters;

/**
 *
 * @author a-skoshy
 */
public class JacItemSimilarity extends ItemSimilarity {

    public JacItemSimilarity() throws SQLException {
        super();

    }

    @Override

    /* Backend process for finding the item to item similarity for all items in the system.
     * Calls the stored procedure and writes results to ItemSimilarity table
     */
    public void calculateAllItemSimilarity(boolean isCategorzied) throws SQLException {
        Connection csCon = DBMSConnection.getConnection();
        CallableStatement cStmt = null;
        String simiSQL = null;
        try {
            if (isCategorzied) {
                simiSQL = "{call CalcSepItemJacSimis()}";
            } else {
                simiSQL = "{call CalcJacSimilarities()}";
            }
            System.out.println(simiSQL);
            cStmt = csCon.prepareCall(simiSQL);
            cStmt.execute();
        } finally {
            DBMSConnection.safeClose(null, cStmt, csCon);
        }
    }

    /*
     *
     * Find all items in the current session
     * Find prediction for each item
     * Find the highest n prediction

     *
     */
    public String findTopPredictedItems(int sessionID) throws Exception {
        System.out.println("findTopPredictedItems " + sessionID);

        QryParameters qParam = QryParameters.getInstance();
        String prediction = qParam.getValue("PREDICTION");
        String colWt = qParam.getValue("COLW");
        String tableWt = qParam.getValue("TABLEW");
        String whereWt = qParam.getValue("WHEREW");

        String topN = qParam.getValue("TOPN");

        String includeAll = "";

        if (prediction.equals("excl")) {
            includeAll = " and b.itemid is null ";
        }

        String itemid = null;
        float predVal = 0.0f;



        String sql = " select itemid, pred from (\n" +
                "select itemid, (case when(dene = 0) then 0 else nume/dene end) pred \n" +
                " from \n" +
                " (select \n" +
                " simi.itemid, \n" +
                " sum(simivalue) dene, \n" +
                " sum(simivalue * (case when (us.itemid is null) then 0 else 1 end) * \n" +
                " (case when ifnull(istable,'N') = 'Y' then " + tableWt + " else \n" +
                "    (case when ifnull(iswhere,'N') = 'Y' then " + whereWt + "  \n" +
                "         else (case when ifnull(iscolumn,'N') = 'Y' then " + colWt + " \n" +
                "               else 1 end) end) end)) nume \n" +
                " from " +
                " (select distinct" +
                " a.itemid,t.istable,t.iscolumn,t.iswhere,t.item2, t.simivalue " +
                " from collabitems a left outer join ( " +
                " select " +
                " q.itemid \n" +
                " from qryitems q, sessionqueries s \n" +
                " where q.queryid= s.queryid and sessionid= ?) b on a.itemid=b.itemid \n" +
                " join (select item1, item2, simivalue,c.istable,c.iscolumn,c.iswhere from topkitemsimilarity tk, collabitems c \n" +
                " where tk.item2 = c.itemid) t on a.itemid=t.item1 and a.istraining = 'Y' " +
                //" where b.itemid is null) simi " + //b.itemid is null picks only the items not in list
                includeAll + " ) simi " +
                " left outer join " +
                " (select distinct itemid \n" + //Distinct is done for an unweighted that is 0 or 1
                " from qryitems q, sessionqueries s \n" +
                " where q.queryid= s.queryid and sessionid= ?)us on simi.item2=us.itemid and us.itemid is not null \n" +
                " group by simi.itemid) predTable " +
                " order by pred desc " +
                " limit " + topN + // Top number of prediction picked
                " )tab where pred > 0";

        System.out.println(sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
        String listStr = "";

        pstmt.setInt(1, sessionID);
        pstmt.setInt(2, sessionID);
        rs = pstmt.executeQuery();
        listStr = "";

        System.out.println("Item Prediction Start ******************");
        while (rs.next()) {
            itemid = rs.getString(1);
            predVal = rs.getFloat(2);
            System.out.println("Item ID:" + itemid + " " + "Predicted Value:" + predVal);
            listStr = listStr + rs.getString(1) + "  ,";
        }
        if (!listStr.equals("")) {
            listStr = listStr.substring(0, listStr.length() - 1);
        }
        System.out.println("Item Prediction End ******************");
        return listStr;
    }

    //Backup of old

    /*public String findTopPredictedItems(int sessionID) throws Exception {
        System.out.println("findTopPredictedItems " + sessionID);

        QryParameters qParam = QryParameters.getInstance();
        String prediction = qParam.getValue("PREDICTION");
        String colWt = qParam.getValue("COLW");
        String tableWt = qParam.getValue("TABLEW");
        String whereWt = qParam.getValue("WHEREW");

        String topN = qParam.getValue("TOPN");

        String includeAll = "";

        if (prediction.equals("excl")) {
            includeAll = " where b.itemid is null ";
        }

        String itemid = null;
        float predVal = 0.0f;



        String sql = " select itemid, pred from (\n" +
                "select itemid, (case when(dene = 0) then 0 else nume/dene end) pred \n" +
                " from \n" +
                " (select \n" +
                " simi.itemid, \n" +
                " sum(simivalue) dene, \n" +
                " sum(simivalue * (case when (us.itemid is null) then 0 else 1 end) * \n" +
                " (case when ifnull(istable,'N') = 'Y' then " + tableWt + " else \n" +
                "    (case when ifnull(iswhere,'N') = 'Y' then " + whereWt + "  \n" +
                "         else (case when ifnull(iscolumn,'N') = 'Y' then " + colWt + " \n" +
                "               else 1 end) end) end)) nume \n" +
                " from " +
                " (select " +
                " a.itemid,a.istable,a.iscolumn,a.iswhere,t.item2, t.simivalue " +
                " from collabitems a left outer join ( " +
                " select " +
                " q.itemid " +
                " from qryitems q, sessionqueries s " +
                " where q.queryid= s.queryid and sessionid= ?) b on a.itemid=b.itemid " +
                " join topkitemsimilarity t on a.itemid=t.item1 " +
                //" where b.itemid is null) simi " + //b.itemid is null picks only the items not in list
                includeAll + " ) simi " +
                " left outer join " +
                " (select distinct itemid " + //Distinct is done for an unweighted that is 0 or 1
                " from qryitems q, sessionqueries s " +
                " where q.queryid= s.queryid and sessionid= ?)us on simi.item2=us.itemid " +
                " group by simi.itemid) predTable " +
                " order by pred desc " +
                " limit " + topN + // Top number of prediction picked
                " )tab where pred > 0";

        System.out.println(sql);
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet rs = null;
        String listStr = "";

        pstmt.setInt(1, sessionID);
        pstmt.setInt(2, sessionID);
        rs = pstmt.executeQuery();
        listStr = "";

        System.out.println("Item Prediction Start ******************");
        while (rs.next()) {
            itemid = rs.getString(1);
            predVal = rs.getFloat(2);
            System.out.println("Item ID:" + itemid + " " + "Predicted Value:" + predVal);
            listStr = listStr + rs.getString(1) + ",";
        }
        if (!listStr.equals("")) {
            listStr = listStr.substring(0, listStr.length() - 1);
        }
        System.out.println("Item Prediction End ******************");
        return listStr;
    }*/
}
